package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;

public class TelephoneDAO {

	private List<Telephone> elementsList;
	private Connection con;
	private DatabaseCommons dbc = new DatabaseCommons();

	public TelephoneDAO() {
		/*
		 * 2013.03.11 - changing ArrayList to LinkedList since the latter is
		 * optimized for addition or subtraction of items
		 */
//		System.out.println("Instantiating the " + TelephoneDAO.class.toString()
//				+ " constructor");
		elementsList = new LinkedList<Telephone>();
	}

	public TelephoneDAO(LinkedList<Telephone> elementsList) {
		super();
		this.elementsList = elementsList;
	}

	public void addElement(Telephone e) {
		elementsList.add(e);
		/*
		 * System.out.println(
		 * "addPerson succeeded!\n*************Person added is:\n" +
		 * "idPerson: " + p.getId() + ", name: " + p.getName() + ", surname: " +
		 * p.getSurname() + ", Collateral client: " + p.getCc() + ", CC desc: "
		 * + p.getColClient() + ", isChkIC? = " + p.isChkIndirectClient());
		 */
	}

	public void removeElement(int index) {
		elementsList.remove(index);
	}

	public List<Telephone> getElements() {
		// Collections.unmodifiableList prevents other classes from modifying
		// the list by the reference retrieved
		return Collections.unmodifiableList(elementsList);
	}

	// public void h2Connect() throws SQLException {
	// // checks the mysql-connector
	// try {
	// Class.forName("org.h2.Driver");
	// } catch (ClassNotFoundException e) {
	// System.out
	// .println("ClassNotFoundException while seeking the H2 driver: "
	// + e);
	// }
	// // create connection
	// String url = "jdbc:h2:~/test", sqlUname = "sa", sqlPass = "";
	// // sqlPass = "r00t";
	//
	// // if connection is already created, just return
	// if (con != null)
	// return;
	// // otherwise, create it
	// con = DriverManager.getConnection(url, sqlUname, sqlPass);
	// System.out.println("Connected: " + con);
	// }
	//
	// public void disconnect() throws SQLException {
	// System.out.println("...closing now.");
	// if (con != null) {
	// con.close();
	// System.out.println("isClosed? " + con.isClosed());
	// }
	// }

	/*
	 * Method to insert and or update a list of people
	 */
	public void saveToDB() throws SQLException {
		String csql, isql, usql;
		int col = 0;
		/*
		 * id will be used to check the id's while I go through the elements
		 * reps to compare occurrences
		 */
		int id = -1, reps = -1;
		/*
		 * sql queries with question marks are used in prepared statements since
		 * they are more secure due to being more difficult to be attacked by a
		 * code injection tactic.
		 */
		csql = "select count(*) as reps from People where id = ?";
		isql = "insert into People (id, name, surname, collateral_client, is_direct_client) values(?, ?, ?, ?, ?)";
		usql = "update People set name = ?, surname = ?, collateral_client = ?, is_direct_client = ? where id = ?";
		PreparedStatement cps = null, eps = null;
		ResultSet rs = null;

		cps = con.prepareStatement(csql);

		for (Telephone x : elementsList) {
			// id = p.getId();

			System.out.println("values created for insertion - id: "
					+ x.getId() + ", idTelephoneType: " + x.getIdType()
					+ ", desc: " + x.getDesc());
			// first parameter is the index of the wildcard, this once there's
			// just one though
			cps.setInt(1, id);
			// execute the query
			rs = cps.executeQuery();
			// go to cursor in the first position
			rs.next();
			// get the count
			reps = rs.getInt("reps");

			if (reps == 0) {
				// preparing the insert with the execution prepared statement
				eps = con.prepareStatement(isql);
				// col will iterate setting values through the column numbers
				// dynamically
				col = 1;
				eps.setInt(col++, x.getId());
				eps.setInt(col++, x.getIdType());
				eps.setString(col++, x.getDesc());

				System.out.println("Inserting element with id: " + id
						+ x.getId() + ", idTelephoneType: " + x.getIdType()
						+ ", desc: " + x.getDesc());
				eps.executeUpdate();
			} else {
				// preparing the update with the execution prepared statement
				eps = con.prepareStatement(usql);
				// col will iterate setting values through the column numbers
				// dynamically
				col = 1;
				eps.setInt(col++, x.getId());
				eps.setInt(col++, x.getIdType());
				eps.setString(col++, x.getDesc());
				eps.setInt(col++, id);

				System.out.println("Updating elements with id: " + id
						+ x.getId() + ", idTelephoneType: " + x.getIdType()
						+ ", desc: " + x.getDesc());
				eps.executeUpdate();
			}
		}
		csql = "cleaned from saveToDB()";
		isql = "cleaned from saveToDB()";
		usql = "cleaned from saveToDB()";
		cps.close();
		eps.close();
	}

	public void loadFromDB() throws SQLException {
		String csql;
		/*
		 * very very first thing, I'll declare my prepared statement
		 */
		csql = "select id, name, surname, collateral_client, is_direct_client from people order by name";
		Statement select = con.createStatement();
		// first thing, I'll clear the list
		elementsList.clear();

		// I execute the query and load it in the rs resultset
		ResultSet rs = select.executeQuery(csql);

		while (rs.next()) {
			/*
			 * System.out.println("id: " + rs.getInt("id") + ", name: " +
			 * rs.getString("name") + ", surname: " + rs.getString("surname"));
			 */

			/*
			 * people.add(new Person(rs.getInt("id"), rs.getString("name"), rs
			 * .getString("surname"), CollateralClient.valueOf(rs
			 * .getString("collateral_client")), rs
			 * .getBoolean("isDirectClient")));
			 */
			Telephone newElement = new Telephone(rs.getInt("id"),
					rs.getInt("idTelephoneType"), rs.getString("desc"));
			System.out.println("retrieving " + Telephone.class.toString()
					+ " element: " + newElement);
			elementsList.add(newElement);
		}

		/*
		 * finally, I close the resultset and the statement
		 */
		csql = "cleaned from loadFromDB()";
		select.close();
		rs.close();

	}

	public void addTelephone(int idTelephoneType, int idPerson, String telephone)
			throws SQLException {
		String isql;
		int col = 0, colClient = 0;
		/*
		 * id will be used to check the id's while I go through the elements
		 * reps to compare occurrences
		 */
		int id = -1, reps = -1, lastId = -1;
		/*
		 * sql queries with question marks are used in prepared statements since
		 * they are more secure due to being more difficult to be attacked by a
		 * code injection tactic.
		 */
		isql = "insert into Telephones(id_telephone_type, id_person, telephone) values(?, ?, ?)";
		PreparedStatement eps = null;

		/*
		 * trying to factor connection methods
		 */
		// dbc.h2Connect();
		con = null;
		con = dbc.getConnection();
		// System.out.println("addTelephoneNoCommit - con: " + con.toString() +
		// ", state: " + con.isClosed());

		eps = con.prepareStatement(isql);
		// int idTelephoneType, int idPerson, String telephone
		eps.setInt(1, idTelephoneType);
		eps.setInt(2, idPerson);
		eps.setString(3, telephone);

		eps.executeUpdate();

		// ResultSet rsGeneratedKey = eps.getGeneratedKeys();
		//
		// if (rsGeneratedKey != null && rsGeneratedKey.next()) {
		// lastId = rsGeneratedKey.getInt(1);
		// }

		// method that gets the last inserted index from the prepared statement
		lastId = dbc.getLastInsertedIndex(eps, "addTelephone(idTelephoneType="
				+ idTelephoneType + ", idPerson=" + idPerson + ", telephone="
				+ telephone);
		System.out.println("last telephone generated id: " + lastId);

		isql = "cleaned from saveToDB()";
		eps.close();
	}

}